using System;

namespace WP.Commons
{
	#region Base DataSourceSql
	interface BaseDataSourceSql
	{
		string  GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition);
	}
	#endregion 

	#region Class DataSourceSql
	/// <summary>
	/// Summary description for DataSourceSql.
	/// </summary>
	public class DataSourceSql
	{
		public DataSourceSql()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public static string GetSql(SystemModules systemModules,string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{

			BaseDataSourceSql dataSourceSql=null;

			switch(systemModules)
			{
				case SystemModules.GL:
					dataSourceSql=new DataSourceSql_GL();
					break;
				case SystemModules.AR:
					dataSourceSql=new DataSourceSql_AR();
					break;
				case SystemModules.AP:
					dataSourceSql=new DataSourceSql_AP();
					break;
				case SystemModules.OE:
					dataSourceSql=new DataSourceSql_OE();
					break;
				case SystemModules.PO:
					dataSourceSql=new DataSourceSql_PO();
					break;
				case SystemModules.MF:
					dataSourceSql=new DataSourceSql_MF();
					break;
				case SystemModules.IM:
					dataSourceSql=new DataSourceSql_IM();
					break;
				case SystemModules.TP:
					dataSourceSql=new DataSourceSql_TP();
					break;
				case SystemModules.OT:
					dataSourceSql=new DataSourceSql_OT();//The Tables use EditPublic Mtn,but not menu in mMenu
					break;
			}

			return dataSourceSql.GetSql(strTableName,strSubType,sqlType,strCondition);
		}

		public static string GetSql(SystemModules systemModules,string strTableName,string strSubType,SqlType sqlType)
		{
			return GetSql(systemModules,strTableName, strSubType,sqlType,"");
		}
		public static string GetSql(SystemModules systemModules,string strTableName,string strSubType)
		{
			return GetSql(systemModules, strTableName, strSubType,SqlType.LooklUp,"");
		}
		public static string GetSql(SystemModules systemModules,string strTableName)
		{
			return GetSql(systemModules, strTableName, "",SqlType.LooklUp,"");
		}
	}
	#endregion

	#region Clase DataSourceSql_GL
	public class DataSourceSql_GL:BaseDataSourceSql
	{
		public DataSourceSql_GL()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{
				case "GLPROFITCENTER"://glProfitCenter
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="select profitno,profdesc from glprofitcenter ORDER BY profitno";
					break;
				case "GLJNLSOURCETYPE"://glJnlSourceType
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="select Source,Description,SourceNo,Type from glJnlSourceType Order by SourceNo";
					break;
				case "GLOPENFILE"://glOpenFile  
					if(strSubType == "SOURCENO" && sqlType == SqlType.LooklUp)
					{
						strSql = "SELECT distinct Source FROM glOpenFile";
					}
					else if(strSubType == "DOCNO" && sqlType == SqlType.LooklUp)
					{
						if(strCondition=="")
							strSql="SELECT DocNo, convert(varchar,DocDate,101) as  DocDate, AcctNo, ProfitNo, BranchNo, Amt FROM glOpenFile";
						else
							strSql="SELECT DocNo, convert(varchar,DocDate,101) as  DocDate, AcctNo, ProfitNo, BranchNo, Amt FROM glOpenFile Where Source='"+strCondition+"'";
					}
					break;
				case "GLSUBDTLINFO"://GlSubDtlInfo
					strSql =  "select Code,Name,Description from GlSubDtlInfo where AcctNo='{0}' and branchno='{1}' and profitNo='{2}'";
					strSql = String.Format(strSql,strCondition.Split(';')[0],strCondition.Split(';')[1],strCondition.Split(';')[2]);
					break;
			
			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_AR
	public class DataSourceSql_AR:BaseDataSourceSql
	{
		public DataSourceSql_AR()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{			
				case "ARSALESPERSON"://ArSalesperson
					if(sqlType == SqlType.LooklUp)
						strSql = "SELECT SlspNo, SlspName, Phone, CommPct, Street, City, ProvNo,email FROM arSalesperson where active=1";
					break;
				case "ARSHIPVIA"://ArShipVia
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT ViaNo, ViaDesc FROM arShipVia order by ViaNo";
					break;
				case "ARSALESPERSONGRP"://ArSalespersonGrp
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT SlspGrp, SlspGrpDesc FROM arSalespersonGrp ORDER BY SlspGrp";
					break;
				case "ARTYPE"://ArType
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT TypeNo,TypeDesc FROM arType ORDER BY TypeNo";
					break;
				case "ARGROUP"://ArGroup
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT GroupNo,GroupDesc FROM arGroup ORDER BY GroupNo";
					break;
				case "ARTERR"://ArTerr
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT TerrNo,TerrDesc FROM arTerr ORDER BY TerrNo";
					break;
				case "ARTRUCK"://ArTruck
					if(sqlType == SqlType.ShowList)
						strSql = "SELECT truckno,truckdesc from artruck order by truckno";
					else if(sqlType == SqlType.Update)
						strSql = "SELECT truckno,truckdesc,MaxAllowWeight,weightUnit from artruck";
					break;
				case "IMPLNGROUP"://imPlnGroup
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT PlGrpNo,PlGrpDesc from imPlnGroup order by PlGrpNo";
					break;

				case "ARCASHHDR"://arcashhdr  frmArCashReceMtn
					if(sqlType == SqlType.ShowList || sqlType == SqlType.LooklUp )
					{
						strSql = "SELECT SeqNo,Type,CustNo,CustName,DepNo,Adjustment,RecptDate,";
                        strSql += " Amt FROM arCashHdr where posted=0 " + BaseFunctions.GetSubCondition("AR");
					}
					else
						strSql = @"SELECT SeqNo,Type,CustNo,CustName,DepNo,Adjustment,RecptDate,
							Amt,CurNo,ExchRate,Remark,InvNo,CheckType,ChqNo,ChqDate,CheckBankNo,BankAcctNo,BankProfitNo,
							BankBranchNo,arAcctNo,arProfitNo,arBranchNo,DiscAcctNo,
							DiscProfitNo,DiscBranchNo,FrtRedtAcctNo,FrtRedtProfitNo,FrtRedtBranchNo,WFAmt,WFAcctNo,WFProfitNo,
							WFBranchNo,EnterBy,,SlspNo01,CommPct01,CommAmt01,SlspNo02,CommPct02,CommAmt02,SlspNo03,CommPct03,
							CommAmt03,SlspNo04,CommPct04,CommAmt04 FROM arCashHdr where posted=0";
					break;
				case "ARADJUSTHDR": //arAdjustHdr frmArAdjustHdrMtn
					if (sqlType == SqlType.ShowList || sqlType == SqlType.LooklUp)
					{
						strSql="SELECT SeqNo,AdjustNo,CustNo,CustName,ApplyTo,AdjustDate,TotAmt FROM arAdjustHdr where Posted=0";
					}
					else if(sqlType==SqlType.Update)
					{
						strSql=@"Select SeqNo,AdjustNo,CustNo,CustName,ApplyTo,AdjustDate,TotAmt,AdjustType,CurNo,ExchRate,AdjustRmk,
							SlspNo01,CommPct01,CommAmt01,SlspNo02,CommPct02,CommAmt02,SlspNo03,CommPct03,CommAmt03,SlspNo04,CommPct04,CommAmt04,
							GenRmk,NetDueDate,DiscDueDate,Catg,TrxType,EnterBy from arAdjustHdr";
					}
					break;				
			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_AP
	public class DataSourceSql_AP:BaseDataSourceSql
	{
		public DataSourceSql_AP()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{
				case "APSUPPLIER":
					if(strSubType == "PURCHASE" && sqlType == SqlType.LooklUp)
					{
						strSql="SELECT poOrderHdr.SupNo,ApSupplier.Supname,poOrderHdr.OrdNo FROM poOrderHdr inner join apsupplier on poOrderHdr.supno=apsupplier.supno  where poOrderHdr.void=0 and poOrderHdr.posted=0 and poOrderHdr.branchno='{0}'";
					}
					else if(strSubType == "SUPINVACTIVE" && sqlType == SqlType.LooklUp)
					{
						strSql="SELECT SupNo, SupName, Contact, SupStatus, Street, City, PostalCode, ProvNo,Fax FROM  apsupplier   where SupStatus = 'A'";
					}
					else if(strSubType == "SUPINVALL" && sqlType == SqlType.LooklUp)
					{
						strSql="SELECT SupNo, SupName, Contact, SupStatus, Street, City, PostalCode, ProvNo,Fax FROM  apsupplier   ";
					}
					break;
				case "APSUPINVHDR"://apSupInvHdr
					if(strSubType == "SUPINVALL" && sqlType == SqlType.LooklUp)
					{
						strSql="SELECT  SeqNo,InvNo,supno,InvDate,InvDesc as Remarks from apSupInvHdr ";
					}
					break;
				case "APGROUP"://apGroup
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="SELECT GroupNo,GroupDesc FROM apGroup ORDER BY GroupNo";
					break;
				case "APTYPE"://apType
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="SELECT TypeNo,TypeDesc FROM apType ORDER BY TypeNo";
					break;
				case "APCONTACT":
					if(strSubType.ToUpper() == "BYSUPNO" && sqlType == SqlType.LooklUp)
					{
						strSql = @"select [SupNo],[Job],[Department],[Contact],[TitlePosition],[Phone],fax AS Fax,[PhoneExt],[EmailAddress]
                     from apContact where supno='{0}'";
						strSql = String.Format(strSql,strCondition);
					}
					break;
				case "APORDFROM":
					if(strSubType.ToUpper() == "BYSUPNO" && sqlType == SqlType.LooklUp)
					{
						strSql = @"select SupNo,SupName,OrdFromNo,OrdFromDesc,Street,City,ProvNo
							from apOrdFrom where supno='{0}' ";
						strSql = String.Format(strSql,strCondition);
					}
					else
					{
						strSql = @"select SupNo,SupName,OrdFromNo,OrdFromDesc,Street,City,ProvNo
							from apOrdFrom  ";
					}
					break;
				case "APTERM":
					strSql = @" SELECT TermNo, TermDesc,NetDays, NetType, DiscDays, DiscType, PayDiscPct,DueDateCutOffDay  From apTerm ";
					break;
				case "APCHQHDR"://apChqHdr
					strSql = @"SELECT SeqNo,SupNo,SupName,CurNo,Amt,ChqNo,ChqDate From apChqHdr where Posted=0 
                  and Type=1 and ChqType=0 and prt=0 and SupNo='{0}'";
					strSql = String.Format(strSql,strCondition);
				break;
			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_OE
	public class DataSourceSql_OE:BaseDataSourceSql
	{
		public DataSourceSql_OE()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{
				case "MFOB"://mFob
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="SELECT FobNo,Description,OeArrBy,PoArrBy FROM mFob order by FobNo";
					break;
				case "MGRADE"://mGrade
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="SELECT GradeNo,Description FROM mGrade order by GradeNo";
					break;
				case "OERETURNREASON"://OeReturnReason
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql="SELECT code,description from OeReturnReason order by Code";
					break;

			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_PO
	public class DataSourceSql_PO:BaseDataSourceSql
	{
		public DataSourceSql_PO()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{
				case "POORDERHDR":
					if(strSubType == "CARNOOE" && sqlType == SqlType.LooklUp)
					{
						strSql = "Select CarNo,CarDesc from tpcarno";
					}
					break;
			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_IM
	public class DataSourceSql_IM:BaseDataSourceSql
	{
		public DataSourceSql_IM()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{					
				case "IMITEMGROUP": 
					strSql = "SELECT GroupNo, GroupDesc FROM imItemGroup order by groupNo";
					break;
				case "IMPLNHDR"://imPlnHdr
					strSql = "Select PlNo,PLDesc,ConversionType  From imPlnHdr";
					break;
				case "IMTAGREMARK"://ImTagRemark
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "Select RemarkCode,Remark from ImTagRemark order by RemarkCode";
					break;
				case "IMUNIT"://ImUnit
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT unit,unitdesc,type FROM imUnit   order by unit";
					break;
				case "MLOCATIONZONE"://mLocationZone
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT ZoneNo, ZonDesc FROM mLocationZone order by ZoneNo";
					break;					
				case "IMINVMASTER"://imInvmaster
					if(strSubType.ToUpper() == "ITEMNO")
					{
						strSql = "SELECT Itemno,rtrim(desc1)+'  '+desc2 as Description,plnNo FROM iminvmaster where itemno in (select itemno from iminvloc where locationno='{0}') and ProdType='P'  ";
						strSql = String.Format(strSql,strCondition);

					}
					else
					{
						strSql = "SELECT Itemno,Desc1,Desc2,PlnNo  from imInvmaster where ItemStatus='A'";
					}
					break;

			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_TP
	public class DataSourceSql_TP:BaseDataSourceSql
	{
		public DataSourceSql_TP()
		{

		}
		public string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{					
				case "TPTRUCKFAXEMAIL"://TpTruckFaxEmail
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT TruckerName,FaxNo,Email FROM TpTruckFaxEmail ORDER BY TruckerName";
					break;

				case "TPCARSIZE"://tpCarSize
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select CarSizeCode,CarSize from tpCarSize Order by CarSizeCode";
					break;

				case "TPTRUCKSIZE"://tpTruckSize
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select TruckSizeCode,TruckSize from tpTruckSize Order by TruckSizeCode";
					break;

				case "TPTRANSPSTATUS"://tpTranspStatus
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "Select SeqNo,ShipStatus,SDesc from tpTranspStatus order by SeqNo";
					break;
				case "TPCARNO"://tpCarNo
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select CarNo,CarDesc from tpCarNo order by CarNo";
					break;
			}

			return strSql;
		}
	}
	#endregion

	#region Clase DataSourceSql_MF
	public class  DataSourceSql_MF:BaseDataSourceSql
	{
		public DataSourceSql_MF()
		{

		}
		public  string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{

            case "MTAXCODE":// mtaxcode
					strSql="select tax,taxdesc from mtaxcode ";
					break;
				case "MCURHIST" : //mProvice  FRMMPROVINCEMTN
										switch(sqlType)
										{
											case SqlType.LooklUp :
												strSql="SELECT LastRateChgDate as [Change Date],CurRateSellProduct as [Receivable Rate],CurRatePurchProduct as [Payable Rate],RecID FROM mCurHist where "+strCondition ;
												break;
											
											default:
												strSql="";
												break;
										}
						break;
				case "MPROVINCE" : //mProvice  FRMMPROVINCEMTN
					switch(sqlType)
					{
						case SqlType.LooklUp :
							strSql="SELECT ProvNo, ProvName,RecID From mProvince ORDER BY ProvNo";
							break;
						case SqlType.Update :
							strSql="SELECT ProvNo, ProvName,GstPct, PstPct, PstOnGst,TaxCode,Country,PstAcctno,PstProfitno, GstAcctno, GstProfitno,HomogeneousTax, EcoAcctno, EcoProfitno From mProvince  ORDER BY ProvNo";
							break;
						default:
							strSql="";
							break;
					}
					break;
				case "MBRANCH"://mBranch
					if(strSubType == "ORDERINV" && sqlType == SqlType.LooklUp)
						strSql =" Select mUserBranch.branchno,m.branchdesc from mUserBranch join mBranch m on m.branchno=mUserBranch.branchno where access=1 and username='" +SysVar.UserName+"' ";
					else if(sqlType == SqlType.LooklUp)
						strSql =" Select branchno,branchdesc from mBranch";	
					break;	
				case "MLOCATION"://mlocation 
					if(sqlType == SqlType.LooklUp)
					{
						strSql="Select LocationNo,LocationDesc from mlocation";
					}
					else
					{
						switch(strSubType)
						{							
							case "BTOB"://fit for "POBTB" DropDownList
								strSql="Select (LocationNo+' - ' +rTrim(LocationDesc) ) as LocationDesc,LocationNo from mlocation where DirectOnly=1 order by locationno";
								break;
							case "BTOBOE"://fit for "OEBTB" DropDownList
								strSql="Select (LocationNo+' - ' +rTrim(LocationDesc) ) as LocationDesc,LocationNo from mlocation where DirectOnly=1 and LocationNo in (select Location from mUserLocation where UserName='{0}' and Access=1) order by locationno";
								strSql=String.Format(strSql,SysVar.UserName);
								break;
							default://fit for common Look up
								strSql="Select  LocationDesc,LocationNo from mlocation where  LocationNo in (select Location from mUserLocation where UserName='{0}' and Access=1 ) ";
								strSql=String.Format(strSql,SysVar.UserName);
								break;
						}
					}
					break;
				case "MSWITCH"://mSwitch
					if(sqlType == SqlType.ShowList)
                        strSql = "SELECT SwitchSys, SwitchName, SwitchDesc, SwitchValue=Case SwitchValue when 1 then 'Yes' else 'No' end, ProgressSwitchName,SwitchNo FROM mSwitch " + BaseFunctions.GetSubCondition(strSubType) + " Order by  SwitchName";
					else if(sqlType == SqlType.Update)
                        strSql = "SELECT SwitchSys, SwitchName, SwitchDesc, SwitchValue, ProgressSwitchName FROM mSwitch " + BaseFunctions.GetSubCondition(strSubType) + "";

					break;
			}

			return strSql;
		}

		//For switch menu

	}
	#endregion

	#region Clase DataSourceSql_OT
	public class  DataSourceSql_OT:BaseDataSourceSql
	{
		public DataSourceSql_OT()
		{

		}
		public  string GetSql(string strTableName,string strSubType,SqlType sqlType,string strCondition)
		{
			string strSql="";
			strTableName=strTableName.Trim().ToUpper();
			strSubType=strSubType.Trim().ToUpper();

			switch(strTableName)
			{
				case "ARRISKRATING"://arRiskRating
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select RiskRating,RiskDesc from arRiskRating Order By RiskRating";
					break;
				case "ARSALESTYPE"://ArSalesType
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT SalesType,Description from arSalesType order by SalesType";
					break;
				case "MDEPARTMENT"://mDepartment
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT Department,DepartmentDesc from mDepartment order by Department";
					break;
				case "ARREFERRAL"://arReferral
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT ReferralCode, ReferralDesc FROM arReferral order by ReferralCode";
					break;
				case "ARSHIPTOGROUP"://arShipToGroup
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT GroupNo,GroupDesc FROM arShipToGroup ORDER BY GroupNo";
					break;
				case "APGROUP":
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT  GroupNo,GroupDesc  FROM apGroup ORDER BY GroupNo";
					break;
				case "APTYPE":
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT  TypeNo,TypeDesc  FROM apType ORDER BY TypeNo";
					break;
				case "MCURRENCY":
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT  CurNo,CurCode,CurRateSellProduct,CurRatePurchProduct  FROM mCurrency ORDER BY CurNo";
					break;
				case "AP1099CATG":
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "SELECT CatgCode ,[Description] as descc FROM Ap1099Catg ORDER BY CatgCode";
					break;
				case "IMTAGGROUP"://ImTagGroup
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select TagGrpNo,Description,ProdWidth,ProdHeight,ProdLength from ImTagGroup ORDER BY TagGrpNo";
					break;
				case "IMITEMCERTTYPE"://imItemCertType
					if(sqlType == SqlType.ShowList || sqlType == SqlType.Update)
						strSql = "select TypeNo,TypeDesc from imItemCertType ORDER BY TypeNo";
					break;
			
				
				

			}

			return strSql;
		}
	}
	#endregion
}
